﻿using System;
using System.Collections.Generic;
using System.Text;
using ConnectLib.DAO;
using MSSQLProvider.DataBase;

namespace ConnectLib.DAL
{
    public class TrackingDal : MSSQLDataAccess
    {
        private static TrackingDal _instance = new TrackingDal();

        public static TrackingDal Instance
        {
            get
            {
                return _instance;
            }
        }

        public int AddTracking(TrackingDao tracking)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("InvId");
                arrParamName.Add("@InvId");
                arrValue.Add(tracking.InvId);

                arrFielName.Add("StaffName");
                arrParamName.Add("@StaffName");
                arrValue.Add(tracking.StaffName);

                arrFielName.Add("LocationDate");
                arrParamName.Add("@LocationDate");
                arrValue.Add(tracking.LocationDate);

                arrFielName.Add("LocationAt");
                arrParamName.Add("@LocationAt");
                arrValue.Add(tracking.LocationAt);

                arrFielName.Add("Status");
                arrParamName.Add("@Status");
                arrValue.Add(tracking.Status);

                var queryBuider = new StringBuilder();
                queryBuider.Append("INSERT INTO [Tracking] (");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    queryBuider.Append(arrFielName[i]);
                    if (i < arrFielName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");
                queryBuider.Append(" VALUES (");
                i = 0;

                for (; i < arrParamName.Count; i++)
                {
                    queryBuider.Append(arrParamName[i]);
                    if (i < arrParamName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateTracking(TrackingDao tracking)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("InvId");
                arrParamName.Add("@InvId");
                arrValue.Add(tracking.InvId);

                arrFielName.Add("StaffName");
                arrParamName.Add("@StaffName");
                arrValue.Add(tracking.StaffName);

                arrFielName.Add("LocationDate");
                arrParamName.Add("@LocationDate");
                arrValue.Add(tracking.LocationDate);

                arrFielName.Add("LocationAt");
                arrParamName.Add("@LocationAt");
                arrValue.Add(tracking.LocationAt);

                arrFielName.Add("Status");
                arrParamName.Add("@Status");
                arrValue.Add(tracking.Status);

                arrFielName.Add("Id");
                arrParamName.Add("@Id");
                arrValue.Add(tracking.Id);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Tracking] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateLocation(long id, DateTime locationtime, string locationat)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("LocationDate");
                arrParamName.Add("@LocationDate");
                arrValue.Add(locationtime);

                arrFielName.Add("LocationAt");
                arrParamName.Add("@LocationAt");
                arrValue.Add(locationat);

                arrFielName.Add("Id");
                arrParamName.Add("@Id");
                arrValue.Add(id);
                
                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Tracking] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateStatus(long id, byte status)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("Status");
                arrParamName.Add("@Status");
                arrValue.Add(status);

                arrFielName.Add("Id");
                arrParamName.Add("@Id");
                arrValue.Add(id);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Tracking] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateStaffName(long id, string staffname)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("StaffName");
                arrParamName.Add("@StaffName");
                arrValue.Add(staffname);

                arrFielName.Add("Id");
                arrParamName.Add("@Id");
                arrValue.Add(id);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Tracking] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool DeleteTrackingItem(long id)
        {
            return ExecuteNonQuery(string.Format("DELETE FROM [Tracking] WHERE Id = '{0}'", id));
        }

        public string DeleteTracking(string[] ids)
        {
            string res = string.Empty;

            foreach (string id in ids)
            {
                if (!ExecuteNonQuery(string.Format("DELETE FROM [Tracking] WHERE Id = {0}", Convert.ToInt64(id))))
                {
                    res += id;
                }
            }

            return res;
        }
        
        public List<TrackingDao> GetTracking(string invid)
        {
            try
            {
                var lsHtTracking = ExecuteArrayHastable(string.Format("SELECT * FROM [Tracking] WITH(NOLOCK) WHERE InvId = '{0}' ORDER BY [LocationDate] ASC", invid));

                if (lsHtTracking != null && lsHtTracking.Count > 0)
                {
                    var lsTracking = new List<TrackingDao>(lsHtTracking.Count);

                    foreach (var tracking in lsHtTracking)
                    {
                        lsTracking.Add(new TrackingDao(tracking));
                    }

                    return lsTracking;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

            return null;
        }

    }
}
